﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace WCFserviceApp_DiaDiem
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service_diadiem" in code, svc and config file together.
    public class Service_diadiem : IService_diadiem
    {
        //Thêm dữ liệu vào Khách Sạn
        public string ThemThongTin(int Maphanloai, string Ten, string Diachi, string Quan, string Thanhpho, string Lng, string Lat, string Lienlac, string Avatar, string Hinhanh, int Trangthai)
        {
            try
            {
               

                    //Mở kết nối tham chiếu từ lớp kết nối dữ liệu
                    Ketnoi.ThietlapKetnoi();
                   
                    //THay doi trang thai
                    SqlDataAdapter adap = new SqlDataAdapter("Select * from TBL_DiaDiem", Ketnoi.CN);
                    DataSet data = new DataSet();
                    adap.Fill(data);
                    foreach (DataRow row in data.Tables[0].Rows)
                    {
                        if (row[3].ToString() == Diachi && row[4].ToString() == Quan && row[5].ToString() == Thanhpho)
                        {
                            SqlCommand command = new SqlCommand("UPDATE [TBL_DiaDiem] SET [TrangThai] = 0 WHERE ID = " + row[0], Ketnoi.CN);
                            command.ExecuteNonQuery();
                        }
                    }
                    //-----------Nhập liệu---------------//
                    SqlCommand cmd = new SqlCommand("Insert into TBL_DiaDiem Values(@ID_PL,@Ten,@DiaChi,@Quan,@ThanhPho,@Lng,@Lat,@LienHe,@Avatar,@HinhAnh,@TrangThai)", Ketnoi.CN);
                    cmd.Parameters.AddWithValue("@ID_PL", Maphanloai);
                    cmd.Parameters.AddWithValue("@Ten", Ten);
                    cmd.Parameters.AddWithValue("@Diachi", Diachi);
                    cmd.Parameters.AddWithValue("@Quan", Quan);
                    cmd.Parameters.AddWithValue("@ThanhPho", Thanhpho);
                    cmd.Parameters.AddWithValue("@Lng", Lng);//kinh do
                    cmd.Parameters.AddWithValue("@Lat", Lat);//Vi do
                    cmd.Parameters.AddWithValue("@LienHe", Lienlac);
                    cmd.Parameters.AddWithValue("@Avatar", Avatar);
                    cmd.Parameters.AddWithValue("@Hinhanh", Hinhanh);
                    cmd.Parameters.AddWithValue("@Trangthai", Trangthai);
                    //-----------------------------------//
                    //-----------Đưa vào CSDL------------//
                    cmd.ExecuteNonQuery();
                    //-----------------------------------//
                    //Hủy kết nối CSDL
                    Ketnoi.HuyKetnoi();
                    return Maphanloai + " " + Ten + " " + Diachi + " " + Quan + " " + Thanhpho + " " + Lng + " " + Lat + " " + Lienlac + " " + Avatar + " " + Hinhanh + " " + Trangthai;

            }
            catch
            {
                // return "Không thành công!";
                CustomFaultMsg error = new CustomFaultMsg { Message = "Không thành công!" };
                throw new FaultException<CustomFaultMsg>(error);
            }

        }


        ////Lấy dữ liệu từ bảng
        //public DataTable TaoBang(string sql)
        //{

        //    Ketnoi.ThietlapKetnoi();
        //    SqlDataAdapter Adapter = new SqlDataAdapter(sql, Ketnoi.CN);
        //    DataTable DTable = new DataTable();
        //    Adapter.Fill(DTable);
        //    Adapter.Update(DTable);
        //    return DTable;

        //}
        //Ham thuc hien lenh executenonquery
        public void ExcuteNonQuery(string sql)
        {
            //Ket noi Database
            Ketnoi.ThietlapKetnoi();
            //Thực hiện insert
            SqlCommand cmd = new SqlCommand(sql, Ketnoi.CN);
            cmd.ExecuteNonQuery();
            //Hủy kết nối Database
            Ketnoi.HuyKetnoi();
            cmd.Dispose();
        }
        
        //Phần kết nối cơ sở dữ liệu
        public class Ketnoi
        {
            private static string StrKetnoi = @"Data Source=JOSEPH-PC\SQLEXPRESS;Initial Catalog=DiaDiem; Integrated Security=True";
            public static SqlConnection CN;
            public static string ChuoiKetnoi()
            {
                return StrKetnoi;
            }
            public static void ThietlapKetnoi()
            {
                try
                {
                    CN = new SqlConnection(StrKetnoi);
                    CN.Open();

                }
                catch
                {

                }
            }
            public static void HuyKetnoi()
            {
                CN.Close();
            }
            public void Check(string Quan, string ThanhPho, string DiaChi)
            {
                ThietlapKetnoi();
                SqlDataAdapter adap = new SqlDataAdapter("Select * from TBL_DiaDiem", CN);
                DataSet data = new DataSet();
                adap.Fill(data);
                foreach (DataRow row in data.Tables[0].Rows)
                {
                    if (row[3].ToString() == DiaChi && row[4].ToString() == Quan && row[5].ToString() == ThanhPho)
                    {
                        SqlCommand command = new SqlCommand("UPDATE [TBL_DiaDiem] SET [TrangThai] = 0 WHERE ID = " + row[0], CN);
                        command.ExecuteNonQuery();

                    }
                }


            }


        }
    }
}
